﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;



namespace Persistencias
{
    public class AccesoaDatos
    {
        public AccesoaDatos() { }

        private string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection _SQLConnection;   // = new SqlConnection();
        DataTable _Tabla = new DataTable();
        SqlCommand command = new SqlCommand();

        /// <summary>
        /// Conectar la base de datos actual
        /// </summary>
        private void Conectar()
        {
            try
            {
                _SQLConnection.ConnectionString = ConnectionString;
                _SQLConnection.Open();
                
            }
            catch (SqlException sqlEx)
            {
                // Try to close the connection
                if (_SQLConnection != null)
                    _SQLConnection.Dispose();

                
                ApplicationException excepcion = new ApplicationException(sqlEx.Message, sqlEx);
                throw excepcion;
            
            }
        }

        /// <summary>
        /// desconectar la base de datos actual
        /// </summary>
        private void Desconectar()
        {
            _SQLConnection.Close();
            _SQLConnection.Dispose();
        }

        /// <summary>
        /// Ejecuta una sentencia select
        /// </summary>
        /// <returns></returns>
        public DataTable EjecutaSelect(string StoredProcedure)//, SqlParameter[] Parameter)
        {
            try
            {
                //SqlDataReader _reader = null;
                _SQLConnection = new SqlConnection(this.ConnectionString);
                command.Connection = _SQLConnection;
                command.CommandText = StoredProcedure;
                command.CommandType = CommandType.StoredProcedure;
                this.Conectar();
                SqlDataAdapter _da = new SqlDataAdapter();
                _da.SelectCommand = command;
                //_reader = command.ExecuteReader();
                _da.Fill(_Tabla);
                return _Tabla;
            }
            catch (SqlException sqlEx)
            {
                ApplicationException excepcion = new ApplicationException(sqlEx.Message, sqlEx);
                throw excepcion;
            }
            finally
            {
                this.Desconectar();
            }
        }

        /// <summary>
        /// Crea un nuevo parametro
        /// </summary>
        /// <param name="_Parametro"></param>
        /// <param name="_value"></param>
        public void CrearParametro(string _Parametro, object _value, SqlDbType Tipo)
        {
            command.Parameters.Add(new SqlParameter(_Parametro, Tipo));
            if (_value != null)
            {
                command.Parameters[_Parametro].Value = _value;
            }
            else
            {
                command.Parameters[_Parametro].Value = DBNull.Value;
            }
        }

        /// <summary>
        /// Ejecuta una sentencia Insert, Update, Delete
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        public int EjecutaSentencia(string StoredProcedure)
        {
            try
            {
                int _res;
                _SQLConnection = new SqlConnection(this.ConnectionString);
                //command = new SqlCommand(StoredProcedure, _SQLConnection);
                command.Connection = _SQLConnection;
                command.CommandText = StoredProcedure;
                command.CommandType = CommandType.StoredProcedure;
                this.Conectar();
                _res = command.ExecuteNonQuery();
                return _res;
            }
            catch (SqlException sqlEx)
            {
                ApplicationException excepcion = new ApplicationException(sqlEx.Message, sqlEx);
                throw excepcion;
            }
            finally
            {
                this.Desconectar();
            }
        }
    }
}

